ALTER PROC dbo.cmsAccountReceiveMoney_Search       
(        
 @Type nvarchar(128) = null ,        
 @SearchDate datetime = null ,        
 @FromDate datetime = null ,        
 @ToDate datetime = null ,        
 @Status nvarchar(128) = null,  
 @CustomerId int = null      
)        
AS        
 DECLARE @sql nvarchar(max);        
 DECLARE @nullDate datetime = '1900-1-1';        
BEGIN        
 SET @sql = 'SELECT arm.*,(SELECT top 1 InvoiceNumber FROM cmsOrders where InvoiceNumber = arm.OrderCode) AS InvoiceNumber,cus.FullName,cus.UserName,cus.CustomerCode ';  
 SET @sql += ' FROM cmsAccountReceiveMoney arm, cmsCustomer cus WHERE arm.ToCustomerId = cus.Id ';      
 if(@Status <> '')        
 BEGIN        
  SET @sql += ' AND Status = ''' + @Status + '''';        
 END  
 IF @CustomerId > 0  
  SET @sql += ' AND ToCustomerId = ' + CONVERT(nvarchar,@CustomerId) + ' ';        
 IF @Type = 'date'        
 BEGIN        
  IF DATEDIFF(day,@nullDate,@SearchDate) > 0        
   SET @sql = @sql + ' AND datediff(day,CreatedDate,''' + CONVERT(nvarchar(100),@SearchDate) + ''')=0 '        
 END        
 ELSE IF @Type = 'fromdate'        
 BEGIN        
  IF DATEDIFF(day,@nullDate,@FromDate) > 0 AND DATEDIFF(day,@nullDate,@ToDate) > 0        
  SET @sql = @sql + ' AND (datediff(day,CreatedDate,''' + CONVERT(nvarchar(100),@FromDate) + ''') <= 0 AND datediff(day,CreatedDate,''' + CONVERT(nvarchar(100),@ToDate) + ''') >= 0) '         
 END        
 EXEC(@sql);        
 PRINT @sql;        
         
END  

GO

CREATE PROC dbo.cmsAccountReceiveMoney_Search       
(        
 @Type nvarchar(128) = null ,        
 @SearchDate datetime = null ,        
 @FromDate datetime = null ,        
 @ToDate datetime = null ,        
 @Status nvarchar(128) = null,  
 @CustomerId int = null      
)        
AS        
 DECLARE @sql nvarchar(max);        
 DECLARE @nullDate datetime = '1900-1-1';        
BEGIN        
 SET @sql = 'SELECT arm.*,(SELECT top 1 InvoiceNumber FROM cmsOrders where InvoiceNumber = arm.OrderCode) AS InvoiceNumber,cus.FullName,cus.UserName,cus.CustomerCode ';  
 SET @sql += ' FROM cmsAccountReceiveMoney arm, cmsCustomer cus WHERE arm.ToCustomerId = cus.Id ';      
 if(@Status <> '')        
 BEGIN        
  SET @sql += ' AND Status = ''' + @Status + '''';        
 END  
 IF @CustomerId > 0  
  SET @sql += ' AND ToCustomerId = ' + CONVERT(nvarchar,@CustomerId) + ' ';        
 IF @Type = 'date'        
 BEGIN        
  IF DATEDIFF(day,@nullDate,@SearchDate) > 0        
   SET @sql = @sql + ' AND datediff(day,CreatedDate,''' + CONVERT(nvarchar(100),@SearchDate) + ''')=0 '        
 END        
 ELSE IF @Type = 'fromdate'        
 BEGIN        
  IF DATEDIFF(day,@nullDate,@FromDate) > 0 AND DATEDIFF(day,@nullDate,@ToDate) > 0        
  SET @sql = @sql + ' AND (datediff(day,CreatedDate,''' + CONVERT(nvarchar(100),@FromDate) + ''') <= 0 AND datediff(day,CreatedDate,''' + CONVERT(nvarchar(100),@ToDate) + ''') >= 0) '         
 END        
 EXEC(@sql);        
 PRINT @sql;        
         
END  
  
  